package fr.aprr.geoloc.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import fr.aprr.geoloc.shared.dto.GpsDto;

@Repository
public class GpsDao {

	@Autowired
	private DataSource dataSource;

	public List<GpsDto> getDataVehicule() {
		String nameTable = "gps_2013_11_08";

		String sql = "Select DISTINCT idvehicule " + "from  " + nameTable
				+ " where (autoroute='A31' AND pk BETWEEN 0 AND 67) "
				+ "OR (autoroute='A311' AND pk BETWEEN 28 AND 33) "
				+ "OR (autoroute='A39' AND pk BETWEEN 0 AND 40)";

		Connection conn = null;

		List<GpsDto> listGpsVehicule = new ArrayList<GpsDto>();

		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {

				GpsDto gpsDto = new GpsDto();
				gpsDto.setIdvehicule(rs.getInt("idvehicule"));
				System.out.println("Server : " + gpsDto.getIdvehicule());
				listGpsVehicule.add(gpsDto);
			}

			rs.close();
			ps.close();

			return listGpsVehicule;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return listGpsVehicule;

	}

	public List<GpsDto> getAllVehicule() {

		String sql = "SELECT * " + "FROM ody$conf_flotte_vehicule as v "
				+ "WHERE v.id_vehicule "
				+ "IN (SELECT DISTINCT idvehicule from gps_2013_11_08 "
				+ "WHERE (autoroute='A31' AND pk BETWEEN 0 AND 67) "
				+ "OR (autoroute='A311' AND pk BETWEEN 28 AND 33) "
				+ "OR (autoroute='A39' AND pk BETWEEN 0 AND 40))";

		Connection conn = null;

		List<GpsDto> allVehicule = new ArrayList<GpsDto>();

		try {
			
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {

				GpsDto gpsDto = new GpsDto();
				
				gpsDto.setId(rs.getInt("code_vehicule"));
				gpsDto.setIdvehicule(rs.getInt("id_vehicule"));
				gpsDto.setName(rs.getString("nom_vehicule"));
				gpsDto.setType(rs.getString("vehicule_famille"));
				
				System.out.println("Server : " + gpsDto.getType());
				
				allVehicule.add(gpsDto);
			}

			rs.close();
			ps.close();


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return allVehicule;
	}
}
